﻿CREATE PROCEDURE [dbo].[Report_CategorySameStoreComparisonBySpace]
	@RangeReviewId int
	, @CategoryId smallint
	, @ShowPlanogramTypes bit	
	, @ExchangeRate smallint = 0
AS
	
declare @startDate DATE = (select FirstMonth from RangeReviews WITH (NOLOCK)  where Id = @RangeReviewId)
declare @totalMonths INT = (select TotalMonths from RangeReviews WITH (NOLOCK)  where Id = @RangeReviewId)
declare @endDate DATE = DATEADD(Month, @totalMonths - 1, @startDate)
declare @totalStores INT = (Select Count(StoreId) from RangeReviewStores WITH (NOLOCK)  where RangeReviewId = @RangeReviewId and IsExcluded = 0)
declare @totalCoverage INT = @totalStores * @totalMonths

begin try drop table #exchangeRates; end try begin catch end catch;

Select 
	months.Date
	, CASE 
		WHEN @ExchangeRate = 0 THEN 1 --Moneda Local
		WHEN @ExchangeRate = 1 THEN ISNULL(ex.Rate,1) --Rate para Average
		WHEN @ExchangeRate = 2 THEN ISNULL(ex.ForexRate,1) --Rate para Same Store
		ELSE 1
	END AS Rate
Into #exchangeRates
From Months months
INNER JOIN dbo.RangeReviews rr WITH (NOLOCK) on rr.Id = @RangeReviewId
INNER JOIN dbo.Currencies cur WITH (NOLOCK) on rr.CountryId = cur.CountryId
LEFT OUTER JOIN dbo.CurrencyExchangeRates ex WITH (NOLOCK) on ex.Date = months.Date and ex.SourceCurrencyId = cur.Id and ex.TargetCurrencyId = rr.TargetCurrencyId
Where months.Date BETWEEN @startDate AND @endDate

SELECT
	ar.Id
	, ar.Code
	, ar.Name
	, ar.Width
	, ar.Delist
	, ar.IsDeletedRangeReview
	, ar.Brand
	, parentCat.Name as Category
	, pl.Name as Location1
	, CASE WHEN @ShowPlanogramTypes = 0 THEN 0 ELSE pla.TypeId END as PlanogramType
	, ISNULL(ar.Width, 0) as Width
	, SUM(spa.Facings) as Facings
	, ISNULL(ar.Width, 0) * SUM(spa.Facings) as LinearSpace
	, Units = CASE WHEN ar.Facings != 0 THEN SUM(spa.Facings) / ar.Facings * sales.Units ELSE 0 END
	, Sales = CASE WHEN ar.Facings != 0 THEN SUM(spa.Facings) / ar.Facings * sales.Sales ELSE 0 END
	, GM = CASE WHEN ar.Facings != 0 THEN SUM(spa.Facings) / ar.Facings * sales.GM ELSE 0 END
	, InventoryCost = CASE WHEN ar.Facings != 0 THEN SUM(spa.Facings) / ar.Facings * sales.InventoryCost ELSE 0 END
	, (SELECT Name + ', ' AS 'text()' FROM Planograms p 
		Inner Join Spaces s on s.PlanogramId = p.Id 
		WHERE (s.ArticleId = ar.Id) FOR XML PATH('')) Planograms
FROM Articles ar WITH (NOLOCK) 
	INNER JOIN Categories cat WITH (NOLOCK) on ar.CategoryId = cat.Id 
	INNER JOIN Categories parentCat WITH (NOLOCK) on cat.ParentId = parentCat.Id
	LEFT JOIN
	(
		Select 
			ar.Id ArticleId
			, CASE WHEN umc.Id IS NOT NULL THEN ISNULL(SUM(cast(Units as money) / umc.Value) / CAST(@totalCoverage AS MONEY),0) ELSE ISNULL(SUM(cast(Units as money)) / CAST(@totalCoverage AS MONEY),0) END AS Units	
			, CASE WHEN co.IsVATIncluded = 0 THEN ISNULL(SUM(Sales * ex.Rate) / CAST(@totalCoverage AS MONEY),0) ELSE ISNULL(SUM(Sales / (1 + ar.TaxRate) * ex.Rate) / CAST(@totalCoverage AS MONEY),0) END AS Sales	
			, CASE 
				WHEN am.Margin is not null THEN ISNULL(SUM(Sales * ex.Rate) / CAST(@totalCoverage AS MONEY),0) * am.Margin 
				WHEN scm.Margin is not null THEN ISNULL(SUM(Sales * ex.Rate) / CAST(@totalCoverage AS MONEY),0) * scm.Margin 
				WHEN cm.Margin is not null THEN ISNULL(SUM(Sales * ex.Rate) / CAST(@totalCoverage AS MONEY),0) * cm.Margin 
				ELSE ISNULL(SUM(GM * ex.Rate) / CAST(@totalCoverage AS MONEY),0) END AS GM
			, ISNULL(SUM(inv.Cost * ex.Rate) / CAST(@totalCoverage AS MONEY),0) as InventoryCost
		From Articles ar WITH (NOLOCK)
		INNER JOIN Categories cat WITH (NOLOCK) on ar.CategoryId = cat.Id 
		INNER JOIN Categories parentCat WITH (NOLOCK) on cat.ParentId = parentCat.Id
		INNER JOIN RangeReviews rr WITH (NOLOCK) on ar.RangeReviewId = rr.Id and rr.Id = @RangeReviewId
		INNER JOIN Countries co WITH (NOLOCK) on rr.CountryId = co.Id
		INNER JOIN Currencies cur WITH (NOLOCK) on rr.CountryId = cur.CountryId
		INNER JOIN Months months WITH (NOLOCK) on (months.Date BETWEEN @startDate AND @endDate)
		INNER JOIN Stores store WITH (NOLOCK) on store.CountryId = co.Id
		LEFT JOIN ArticleSales sales WITH (NOLOCK) on sales.ArticleId = ar.Id 
			AND sales.Date = months.Date 
			AND sales.Confirmed = 1
			AND sales.StoreId = store.Id
		LEFT JOIN Inventories inv WITH (NOLOCK) on inv.ArticleId = ar.Id 
			AND inv.Date = months.Date 
			AND inv.StoreId = store.Id
		--LEFT JOIN CurrencyExchangeRates ex WITH (NOLOCK) on ex.Date = months.Date and ex.SourceCurrencyId = cur.Id and ex.TargetCurrencyId = rr.TargetCurrencyId
		INNER JOIN #exchangeRates ex on Months.Date = ex.Date
		LEFT JOIN TheoreticalMargins am WITH (NOLOCK)  on am.CountryId = co.Id AND  am.ArticleId = ar.Id  
		LEFT JOIN TheoreticalMargins scm WITH (NOLOCK) on scm.CountryId = co.Id AND (scm.ArticleId is null AND scm.CategoryId = cat.Id) 
		LEFT JOIN TheoreticalMargins cm WITH (NOLOCK) on cm.CountryId = co.Id AND (cm.ArticleId is null AND cm.CategoryId <> cat.Id AND cm.CategoryId = cat.ParentId)
		LEFT JOIN UnitOfMeasureConvertions umc WITH (NOLOCK) on ar.UnitOfMeasureConvertionId = umc.Id and ar.UOM = umc.SourceUnit
		Where 
			store.Id in (select StoreId from RangeReviewStores where RangeReviewId = @RangeReviewId and IsExcluded = 0)
			AND parentCat.ParentId = 6
			AND (@CategoryId = 0 OR (@CategoryId != 0 AND (ar.CategoryId = @CategoryId OR cat.ParentId = @CategoryId)))
		Group By 
			ar.Id
			, am.Margin
			, scm.Margin
			, cm.Margin
			, umc.Id
			, co.IsVATIncluded
	) sales on ar.Id = sales.ArticleId 
	LEFT JOIN Spaces spa WITH (NOLOCK) on spa.ArticleId = ar.Id and spa.RangeReviewId = @RangeReviewId
	LEFT JOIN Planograms pla WITH (NOLOCK) on spa.PlanogramId = pla.Id
	LEFT JOIN PlanogramLocations pl WITH (NOLOCK) on pla.PlanogramLocationId = pl.Id	
Where
	ar.RangeReviewId = @RangeReviewId 
	and ar.IsDeletedPerformance = 0
	and (sales > 0 or (ar.Width * ar.Facings) > 0)
	and (@CategoryId = 0 OR (@CategoryId != 0 AND (ar.CategoryId = @CategoryId OR cat.ParentId = @CategoryId)))
Group by
	ar.Id
	, ar.Code
	, ar.Name
	, ar.Width
	, ar.Facings
	, ar.Delist
	, ar.IsDeletedRangeReview
	, ar.Brand
	, parentCat.Name
	, pl.Name
	, CASE WHEN @ShowPlanogramTypes = 0 THEN 0 ELSE pla.TypeId END
	, sales.Units
	, sales.Sales
	, sales.GM
	, sales.InventoryCost
Order By
	Sales DESC
	
DROP TABLE #exchangeRates